/**
 * 审计追踪数据库架构
 * EndoSight-UC 医疗AI系统
 *
 * 提供完整的审计追踪数据库表结构
 */

// 审计日志表（不可修改）
export const CREATE_AUDIT_LOGS_TABLE = `
CREATE TABLE IF NOT EXISTS audit_logs (
  id TEXT PRIMARY KEY,
  timestamp DATETIME NOT NULL,
  level TEXT NOT NULL CHECK (level IN ('critical', 'error', 'warning', 'info', 'debug', 'audit', 'security', 'compliance')),
  correlation_id TEXT,
  user_id TEXT,
  session_id TEXT,
  message TEXT NOT NULL,
  action TEXT,
  resource_type TEXT,
  resource_id TEXT,
  ip_address TEXT,
  user_agent TEXT,
  log_data JSON,
  compliance_flags JSON,
  security_flags JSON,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  -- 确保审计日志不可修改
  immutable BOOLEAN DEFAULT 1,
  -- 审计日志完整性校验
  checksum TEXT,
  FOREIGN KEY (user_id) REFERENCES doctors(doctor_id) ON DELETE SET NULL
);

-- 审计日志完整性索引
CREATE INDEX IF NOT EXISTS idx_audit_logs_timestamp ON audit_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_session_id ON audit_logs(session_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_level ON audit_logs(level);
CREATE INDEX IF NOT EXISTS idx_audit_logs_action ON audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_compliance ON audit_logs(compliance_flags);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at);
`;

// 用户会话表
export const CREATE_USER_SESSIONS_TABLE = `
CREATE TABLE IF NOT EXISTS user_sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  login_time DATETIME NOT NULL,
  logout_time DATETIME,
  last_activity DATETIME NOT NULL,
  ip_address TEXT,
  user_agent TEXT,
  session_status TEXT DEFAULT 'active' CHECK (session_status IN ('active', 'expired', 'terminated', 'locked')),
  login_method TEXT CHECK (login_method IN ('password', 'sso', 'api_key', 'token')),
  mfa_verified BOOLEAN DEFAULT 0,
  security_flags JSON,
  session_data JSON,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES doctors(doctor_id) ON DELETE CASCADE
);

-- 用户会话索引
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_login_time ON user_sessions(login_time);
CREATE INDEX IF NOT EXISTS idx_user_sessions_status ON user_sessions(session_status);
CREATE INDEX IF NOT EXISTS idx_user_sessions_ip_address ON user_sessions(ip_address);
CREATE INDEX IF NOT EXISTS idx_user_sessions_last_activity ON user_sessions(last_activity);
`;

// 医疗数据访问日志表
export const CREATE_MEDICAL_DATA_ACCESS_LOGS_TABLE = `
CREATE TABLE IF NOT EXISTS medical_data_access_logs (
  id TEXT PRIMARY KEY,
  audit_log_id TEXT NOT NULL,
  timestamp DATETIME NOT NULL,
  user_id TEXT NOT NULL,
  session_id TEXT,
  patient_id TEXT NOT NULL,
  data_type TEXT NOT NULL CHECK (data_type IN ('patient_record', 'medical_image', 'ai_result', 'scoring_result', 'clinical_note', 'lab_result')),
  access_type TEXT NOT NULL CHECK (access_type IN ('view', 'create', 'update', 'delete', 'export', 'print', 'share')),
  access_reason TEXT,
  consent_verified BOOLEAN DEFAULT 0,
  consent_type TEXT,
  phi_present BOOLEAN DEFAULT 1,
  phi_fields JSON,
  data_before JSON,
  data_after JSON,
  access_granted BOOLEAN DEFAULT 1,
  access_denied_reason TEXT,
  retention_required BOOLEAN DEFAULT 1,
  retention_until DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (audit_log_id) REFERENCES audit_logs(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES doctors(doctor_id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES user_sessions(id) ON DELETE SET NULL,
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE
);

-- 医疗数据访问索引
CREATE INDEX IF NOT EXISTS idx_medical_access_timestamp ON medical_data_access_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_medical_access_user_id ON medical_data_access_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_medical_access_patient_id ON medical_data_access_logs(patient_id);
CREATE INDEX IF NOT EXISTS idx_medical_access_data_type ON medical_data_access_logs(data_type);
CREATE INDEX IF NOT EXISTS idx_medical_access_access_type ON medical_data_access_logs(access_type);
CREATE INDEX IF NOT EXISTS idx_medical_access_consent_verified ON medical_data_access_logs(consent_verified);
CREATE INDEX IF NOT EXISTS idx_medical_access_phi_present ON medical_data_access_logs(phi_present);
`;

// AI推理审计表
export const CREATE_AI_INFERENCE_AUDIT_TABLE = `
CREATE TABLE IF NOT EXISTS ai_inference_audit (
  id TEXT PRIMARY KEY,
  audit_log_id TEXT NOT NULL,
  timestamp DATETIME NOT NULL,
  user_id TEXT,
  session_id TEXT,
  patient_id TEXT,
  task_id TEXT,
  model_version TEXT NOT NULL,
  model_name TEXT NOT NULL,
  input_data_hash TEXT,
  output_data_hash TEXT,
  inference_type TEXT CHECK (inference_type IN ('scoring', 'diagnosis', 'analysis', 'classification')),
  confidence_score REAL,
  processing_time_ms INTEGER,
  input_size_bytes INTEGER,
  output_size_bytes INTEGER,
  inference_status TEXT CHECK (inference_status IN ('success', 'failure', 'timeout', 'rejected')),
  error_message TEXT,
  clinical_decision BOOLEAN DEFAULT 0,
  review_required BOOLEAN DEFAULT 0,
  reviewed_by TEXT,
  review_notes TEXT,
  model_config JSON,
  input_metadata JSON,
  output_metadata JSON,
  compliance_flags JSON,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (audit_log_id) REFERENCES audit_logs(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES doctors(doctor_id) ON DELETE SET NULL,
  FOREIGN KEY (session_id) REFERENCES user_sessions(id) ON DELETE SET NULL,
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE SET NULL,
  FOREIGN KEY (task_id) REFERENCES scoring_tasks(task_id) ON DELETE SET NULL
);

-- AI推理审计索引
CREATE INDEX IF NOT EXISTS idx_ai_inference_timestamp ON ai_inference_audit(timestamp);
CREATE INDEX IF NOT EXISTS idx_ai_inference_user_id ON ai_inference_audit(user_id);
CREATE INDEX IF NOT EXISTS idx_ai_inference_patient_id ON ai_inference_audit(patient_id);
CREATE INDEX IF NOT EXISTS idx_ai_inference_model_version ON ai_inference_audit(model_version);
CREATE INDEX IF NOT EXISTS idx_ai_inference_inference_type ON ai_inference_audit(inference_type);
CREATE INDEX IF NOT EXISTS idx_ai_inference_confidence_score ON ai_inference_audit(confidence_score);
CREATE INDEX IF NOT EXISTS idx_ai_inference_clinical_decision ON ai_inference_audit(clinical_decision);
`;

// 文件操作审计表
export const CREATE_FILE_OPERATION_AUDIT_TABLE = `
CREATE TABLE IF NOT EXISTS file_operation_audit (
  id TEXT PRIMARY KEY,
  audit_log_id TEXT NOT NULL,
  timestamp DATETIME NOT NULL,
  user_id TEXT,
  session_id TEXT,
  operation_type TEXT NOT NULL CHECK (operation_type IN ('upload', 'download', 'view', 'delete', 'modify', 'copy', 'move', 'share', 'export')),
  file_name TEXT NOT NULL,
  file_path TEXT,
  file_type TEXT,
  file_size_bytes INTEGER,
  file_hash TEXT,
  mime_type TEXT,
  medical_image BOOLEAN DEFAULT 0,
  patient_id TEXT,
  task_id TEXT,
  upload_result JSON,
  validation_result JSON,
  storage_location TEXT,
  access_granted BOOLEAN DEFAULT 1,
  access_denied_reason TEXT,
  retention_required BOOLEAN DEFAULT 1,
  retention_until DATETIME,
  gdpr_data BOOLEAN DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (audit_log_id) REFERENCES audit_logs(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES doctors(doctor_id) ON DELETE SET NULL,
  FOREIGN KEY (session_id) REFERENCES user_sessions(id) ON DELETE SET NULL,
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE SET NULL,
  FOREIGN KEY (task_id) REFERENCES scoring_tasks(task_id) ON DELETE SET NULL
);

-- 文件操作审计索引
CREATE INDEX IF NOT EXISTS idx_file_operation_timestamp ON file_operation_audit(timestamp);
CREATE INDEX IF NOT EXISTS idx_file_operation_user_id ON file_operation_audit(user_id);
CREATE INDEX IF NOT EXISTS idx_file_operation_operation_type ON file_operation_audit(operation_type);
CREATE INDEX IF NOT EXISTS idx_file_operation_medical_image ON file_operation_audit(medical_image);
CREATE INDEX IF NOT EXISTS idx_file_operation_patient_id ON file_operation_audit(patient_id);
CREATE INDEX IF NOT EXISTS idx_file_operation_file_hash ON file_operation_audit(file_hash);
`;

// 合规违规记录表
export const CREATE_COMPLIANCE_VIOLATIONS_TABLE = `
CREATE TABLE IF NOT EXISTS compliance_violations (
  id TEXT PRIMARY KEY,
  audit_log_id TEXT NOT NULL,
  timestamp DATETIME NOT NULL,
  violation_type TEXT NOT NULL CHECK (violation_type IN ('phi_breach', 'access_violation', 'consent_missing', 'retention_exceeded', 'audit_tampering', 'unauthorized_export')),
  severity TEXT CHECK (severity IN ('low', 'medium', 'high', 'critical')),
  regulation TEXT DEFAULT 'HIPAA',
  regulation_section TEXT,
  description TEXT NOT NULL,
  user_id TEXT,
  patient_id TEXT,
  data_affected JSON,
  remediation_required BOOLEAN DEFAULT 1,
  remediation_status TEXT DEFAULT 'pending' CHECK (remediation_status IN ('pending', 'in_progress', 'completed', 'waived')),
  remediation_due_date DATETIME,
  reported_to_authorities BOOLEAN DEFAULT 0,
  report_date DATETIME,
  investigation_status TEXT DEFAULT 'not_started',
  investigation_notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (audit_log_id) REFERENCES audit_logs(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES doctors(doctor_id) ON DELETE SET NULL,
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE SET NULL
);

-- 合规违规索引
CREATE INDEX IF NOT EXISTS idx_compliance_violations_timestamp ON compliance_violations(timestamp);
CREATE INDEX IF NOT EXISTS idx_compliance_violations_violation_type ON compliance_violations(violation_type);
CREATE INDEX IF NOT EXISTS idx_compliance_violations_severity ON compliance_violations(severity);
CREATE INDEX IF NOT EXISTS idx_compliance_violations_user_id ON compliance_violations(user_id);
CREATE INDEX IF NOT EXISTS idx_compliance_violations_patient_id ON compliance_violations(patient_id);
CREATE INDEX IF NOT EXISTS idx_compliance_violations_status ON compliance_violations(remediation_status);
`;

// 系统配置变更审计表
export const CREATE_SYSTEM_CONFIG_AUDIT_TABLE = `
CREATE TABLE IF NOT EXISTS system_config_audit (
  id TEXT PRIMARY KEY,
  audit_log_id TEXT NOT NULL,
  timestamp DATETIME NOT NULL,
  user_id TEXT,
  session_id TEXT,
  config_category TEXT NOT NULL CHECK (config_category IN ('security', 'ai_model', 'database', 'logging', 'compliance', 'system')),
  config_key TEXT NOT NULL,
  old_value JSON,
  new_value JSON,
  change_reason TEXT,
  approval_required BOOLEAN DEFAULT 0,
  approved_by TEXT,
  approval_timestamp DATETIME,
  rollback_possible BOOLEAN DEFAULT 1,
  rollback_completed BOOLEAN DEFAULT 0,
  rollback_timestamp DATETIME,
  impact_assessment JSON,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (audit_log_id) REFERENCES audit_logs(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES doctors(doctor_id) ON DELETE SET NULL,
  FOREIGN KEY (session_id) REFERENCES user_sessions(id) ON DELETE SET NULL,
  FOREIGN KEY (approved_by) REFERENCES doctors(doctor_id) ON DELETE SET NULL
);

-- 系统配置变更索引
CREATE INDEX IF NOT EXISTS idx_system_config_timestamp ON system_config_audit(timestamp);
CREATE INDEX IF NOT EXISTS idx_system_config_user_id ON system_config_audit(user_id);
CREATE INDEX IF NOT EXISTS idx_system_config_category ON system_config_audit(config_category);
CREATE INDEX IF NOT EXISTS idx_system_config_key ON system_config_audit(config_key);
`;

// 数据导出审计表
export const CREATE_DATA_EXPORT_AUDIT_TABLE = `
CREATE TABLE IF NOT EXISTS data_export_audit (
  id TEXT PRIMARY KEY,
  audit_log_id TEXT NOT NULL,
  timestamp DATETIME NOT NULL,
  user_id TEXT NOT NULL,
  session_id TEXT,
  export_type TEXT NOT NULL CHECK (export_type IN ('patient_records', 'audit_logs', 'analytics', 'backup', 'research')),
  export_format TEXT CHECK (export_format IN ('json', 'csv', 'pdf', 'dicom', 'zip')),
  data_scope JSON,
  record_count INTEGER,
  file_size_bytes INTEGER,
  export_file_path TEXT,
  export_file_hash TEXT,
  includes_phi BOOLEAN DEFAULT 0,
  phi_count INTEGER DEFAULT 0,
  consent_verified BOOLEAN DEFAULT 0,
  purpose TEXT,
  recipient_info TEXT,
  approved BOOLEAN DEFAULT 0,
  approved_by TEXT,
  approval_reason TEXT,
  auto_delete BOOLEAN DEFAULT 1,
  delete_after_days INTEGER DEFAULT 30,
  deleted_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (audit_log_id) REFERENCES audit_logs(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES doctors(doctor_id) ON DELETE CASCADE,
  FOREIGN KEY (session_id) REFERENCES user_sessions(id) ON DELETE SET NULL,
  FOREIGN KEY (approved_by) REFERENCES doctors(doctor_id) ON DELETE SET NULL
);

-- 数据导出审计索引
CREATE INDEX IF NOT EXISTS idx_data_export_timestamp ON data_export_audit(timestamp);
CREATE INDEX IF NOT EXISTS idx_data_export_user_id ON data_export_audit(user_id);
CREATE INDEX IF NOT EXISTS idx_data_export_export_type ON data_export_audit(export_type);
CREATE INDEX IF NOT EXISTS idx_data_export_includes_phi ON data_export_audit(includes_phi);
CREATE INDEX IF NOT EXISTS idx_data_export_approved ON data_export_audit(approved);
`;

// 初始化所有审计表
export const initializeAuditTables = async (db) => {
  const tables = [
    CREATE_AUDIT_LOGS_TABLE,
    CREATE_USER_SESSIONS_TABLE,
    CREATE_MEDICAL_DATA_ACCESS_LOGS_TABLE,
    CREATE_AI_INFERENCE_AUDIT_TABLE,
    CREATE_FILE_OPERATION_AUDIT_TABLE,
    CREATE_COMPLIANCE_VIOLATIONS_TABLE,
    CREATE_SYSTEM_CONFIG_AUDIT_TABLE,
    CREATE_DATA_EXPORT_AUDIT_TABLE
  ];

  try {
    for (const tableSQL of tables) {
      await db.execute(tableSQL);
    }

    return {
      success: true,
      message: '所有审计表初始化成功',
      tables: tables.length
    };
  } catch (error) {
    throw new Error(`审计表初始化失败: ${error.message}`);
  }
};

// 数据清理策略（根据合规要求）
export const createCleanupProcedures = `
-- 自动清理过期的用户会话
CREATE TRIGGER IF NOT EXISTS cleanup_expired_sessions
AFTER INSERT ON user_sessions
BEGIN
  DELETE FROM user_sessions
  WHERE session_status = 'expired'
    AND logout_time < datetime('now', '-30 days');
END;

-- 自动删除临时导出文件
CREATE TRIGGER IF NOT EXISTS cleanup_temporary_exports
AFTER INSERT ON data_export_audit
WHEN NEW.auto_delete = 1
BEGIN
  UPDATE data_export_audit
  SET deleted_at = datetime('now', '+' || NEW.delete_after_days || ' days')
  WHERE id = NEW.id AND deleted_at IS NULL;
END;
`;

export default {
  CREATE_AUDIT_LOGS_TABLE,
  CREATE_USER_SESSIONS_TABLE,
  CREATE_MEDICAL_DATA_ACCESS_LOGS_TABLE,
  CREATE_AI_INFERENCE_AUDIT_TABLE,
  CREATE_FILE_OPERATION_AUDIT_TABLE,
  CREATE_COMPLIANCE_VIOLATIONS_TABLE,
  CREATE_SYSTEM_CONFIG_AUDIT_TABLE,
  CREATE_DATA_EXPORT_AUDIT_TABLE,
  initializeAuditTables,
  createCleanupProcedures
};